Amazon Athena でデータがパーティション分割されていないログを分析する

Takaaki Kakei

2019.12.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

「Amazon Athena でデータがパーティション分割されていないログを分析する方法がよく分からない。。」

とお悩みの方いませんか。以下の公式ドキュメントでは ELB のログで紹介されていましたが、その他のソースで事例が少なく行き詰まりやすいポイントかと思ったので書きます。

[データのパーティション分割] - [シナリオ 2: データがパーティション分割されていない場合。]

パーティションとは？

まずはパーティションの重要性を理解しておきましょう！

Athenaでパーティションを指定することで、各クエリで特定の領域に対するスキャンを実行できるようになります。例えば、S3上のデータがs3://applog/YYYY/MM/DD/HHのような形でS3へ保存するとします。パーティションを指定することで、検索時に特定の日付配下のデータだけスキャンすることができるので、パフォーマンスが向上します。また、スキャンするデータ量も減るため、コストも削減できます。

Amazon Athenaのパーティションを理解する #reinvent

やってみた

パーティションの重要性を理解したところで、以下の要件で環境構築していきます。

分析するログは S3 サーバアクセスログを対象
複数サーバアクセスログを一つのバケットに出力
- サーバアクセスログ取得対象の S3 バケットは2つ（以降、バケットA・バケットB）
- サーバアクセスログ出力先の S3 バケットは1つ（以降、バケットC)
- バケットCのパスは、本ブログでは仮で s3://demo/ とする
Athena で各バケットのログ単位でパーティションを分けて分析できるようにする

尚、本運用で複数バケットのログを一つのバケットに集約するのは、スキャンコストを考慮してあまりオススメしません。上記要件はサンプルとしてご認識ください。

S3 バケットとサーバアクセスログを用意する

バケット作成

S3 バケットを作成する方法を参考にバケットA〜Cを作成します。バケット名は任意、リージョンは[アジアパシフィック (東京)]で、その他のオプションはデフォルトで結構です。

サーバアクセスログの記録

Amazon S3 サーバーアクセスのログ記録を参考に、バケットA,Bにおいてサーバアクセスログの有効化設定をします。尚、サーバアクセスのログ記録の設定は有効化後、反映に少し時間がかかります(約１時間ほど)。

ターゲットバケット：バケットC
ターゲットプレフィックス：
- バケットAの場合： accesslog1
- バケットBの場合： accesslog2

テストファイルのアップロード

後工程のログ分析の為に、test.mdというファイル作成して、バケットA,Bにアップロードしておきます。

Athena の Database と Tables を用意する

Database 作成

Athena コンソールを開き、画面上の Query Editor で次のコマンドを[Run Query]して Database を作成します。

create database demo_db

Table 作成

Query Editor で次のコマンドを[Run Query]して Table を作成します。コマンドの詳細は以下を参照してください。

CREATE TABLE

Athena を使用して Amazon S3 サーバーのアクセスログを分析するにはどうすればよいですか ?

CREATE EXTERNAL TABLE IF NOT EXISTS demo_db.mybucket_logs(
         BucketOwner STRING,
         Bucket STRING,
         RequestDateTime STRING,
         RemoteIP STRING,
         Requester STRING,
         RequestID STRING,
         Operation STRING,
         Key STRING,
         RequestURI_operation STRING,
         RequestURI_key STRING,
         RequestURI_httpProtoversion STRING,
         HTTPstatus STRING,
         ErrorCode STRING,
         BytesSent BIGINT,
         ObjectSize BIGINT,
         TotalTime STRING,
         TurnAroundTime STRING,
         Referrer STRING,
         UserAgent STRING,
         VersionId STRING,
         HostId STRING,
         SigV STRING,
         CipherSuite STRING,
         AuthType STRING,
         EndPoint STRING,
         TLSVersion STRING
) 
PARTITIONED BY (BucketTargetPrefix STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1', 'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$' )
LOCATION 's3://demo/'

1行目で Database (demo_db)に、mybucket_logs という Table を作成するように書いています。

demo_db.mybucket_logs

29行目の PARTITIONED BY句で指定するカラム列名(今回は、BucketTargetPrefix)は、テーブルのカラム列名と被らないように注意しましょう。

PARTITIONED BY (BucketTargetPrefix STRING)

パーティション化された列はテーブルデータ内には存在しません。テーブル列と同じ col_name の値を使用する場合は、エラーになります。

パラメータ - [PARTITIONED BY (col_name data_type [ COMMENT col_comment ], ... ) ]

33行目の LOCATION句で指定するパスは、バケットC のパスです。

LOCATION 's3://demo/'

結果確認

問題なく進んでいれば、以下のように Database と Table が作成されています。

パーティションを手動追加する

データがパーティション分割されていないログの場合は、各パーティションを手動で追加する必要があります。今回の場合だと、accesslog1,accesslog2 という単位でパーティションを追加したいので、Query Editor で次のコマンドを[Run Query]します。locationの後に記載するパスは's3://demo/'のパスは適宜バケットC のパスに変更します。

ALTER TABLE demo_db.mybucket_logs ADD PARTITION (BucketTargetPrefix='accesslog1') location 's3://demo/accesslog1/';

ALTER TABLE demo_db.mybucket_logs ADD PARTITION (BucketTargetPrefix='accesslog2') location 's3://demo/accesslog2/';

分析する

一括解析結果

Query Editor で次のコマンドを入力して、[Run Query]を押下します。2つのバケットにて、test.mdがPUTされていることが分かります。

SELECT "requestdatetime", "key", "requesturi_operation", "buckettargetprefix"
FROM "demo_db"."mybucket_logs" 
WHERE key = 'test.md'
limit 10;

パーティション分割して解析した結果

Query Editor で次のコマンドを入力して、[Run Query]を押下します。３行目でaccesslog1(バケットAのログ)のみを指定しており、結果にも反映されていることが分かります。

SELECT "requestdatetime", "key", "requesturi_operation", "buckettargetprefix"
FROM "demo_db"."mybucket_logs" 
WHERE BucketTargetPrefix = 'accesslog1' and key = 'test.md'
limit 10;